package com.edu.dao;

import com.edu.bean.StudentScore;
import com.edu.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentScoreDao {

    /**
     * 添加学生成绩
     */
    public boolean add(StudentScore score) {
        String sql = "INSERT INTO student_score (student_id, subject_id, score, create_time) VALUES (?, ?, ?, NOW())";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, score.getStudentId());
            stmt.setInt(2, score.getSubjectId());
            stmt.setDouble(3, score.getScore());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 更新学生成绩
     */
    public boolean update(StudentScore score) {
        String sql = "UPDATE student_score SET score = ?, update_time = NOW() WHERE score_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setDouble(1, score.getScore());
            stmt.setInt(2, score.getScoreId());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 删除学生成绩
     */
    public boolean delete(Integer scoreId) {
        String sql = "DELETE FROM student_score WHERE score_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, scoreId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 查询所有学生成绩（包含学生姓名和学科名称）
     */
    public List<StudentScore> findAll() {
        String sql = "SELECT ss.*, u.real_name as student_name, si.subject_name " +
                "FROM student_score ss " +
                "LEFT JOIN user u ON ss.student_id = u.user_id " +
                "LEFT JOIN subject_info si ON ss.subject_id = si.subject_id " +
                "ORDER BY ss.score_id DESC";
        List<StudentScore> scoreList = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                scoreList.add(convertToStudentScore(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return scoreList;
    }

    /**
     * 根据学科ID查询所有学生成绩
     */
    public List<StudentScore> findBySubjectId(Integer subjectId) {
        String sql = "SELECT ss.*, u.real_name as student_name, si.subject_name " +
                "FROM student_score ss " +
                "LEFT JOIN user u ON ss.student_id = u.user_id " +
                "LEFT JOIN subject_info si ON ss.subject_id = si.subject_id " +
                "WHERE ss.subject_id = ? " +
                "ORDER BY ss.score_id DESC";
        List<StudentScore> scoreList = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, subjectId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                scoreList.add(convertToStudentScore(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return scoreList;
    }

    /**
     * 根据ID查询学生成绩
     */
    public StudentScore findById(Integer scoreId) {
        String sql = "SELECT ss.*, u.real_name as student_name, si.subject_name " +
                "FROM student_score ss " +
                "LEFT JOIN user u ON ss.student_id = u.user_id " +
                "LEFT JOIN subject_info si ON ss.subject_id = si.subject_id " +
                "WHERE ss.score_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, scoreId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return convertToStudentScore(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 检查学生是否已有该学科的成绩
     */
    public boolean hasScore(Integer studentId, Integer subjectId) {
        String sql = "SELECT COUNT(*) FROM student_score WHERE student_id = ? AND subject_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, studentId);
            stmt.setInt(2, subjectId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 将ResultSet转换为StudentScore对象
     */
    private StudentScore convertToStudentScore(ResultSet rs) throws SQLException {
        StudentScore score = new StudentScore();
        score.setScoreId(rs.getInt("score_id"));
        score.setStudentId(rs.getInt("student_id"));
        score.setSubjectId(rs.getInt("subject_id"));
        score.setScore(rs.getDouble("score"));
        score.setCreateTime(rs.getTimestamp("create_time"));
        score.setUpdateTime(rs.getTimestamp("update_time"));
        score.setStudentName(rs.getString("student_name"));
        score.setSubjectName(rs.getString("subject_name"));
        return score;
    }

    /**
     * 分页查询成绩信息
     */
    public List<StudentScore> findByPage(Integer subjectId, int start, int pageSize) {
        String sql = "SELECT ss.*, u.real_name as student_name, si.subject_name " +
                "FROM student_score ss " +
                "LEFT JOIN user u ON ss.student_id = u.user_id " +
                "LEFT JOIN subject_info si ON ss.subject_id = si.subject_id ";

        if (subjectId != null) {
            sql += "WHERE ss.subject_id = ? ";
        }

        sql += "ORDER BY ss.score_id DESC LIMIT ?, ?";

        List<StudentScore> scoreList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            if (subjectId != null) {
                stmt.setInt(1, subjectId);
                stmt.setInt(2, start);
                stmt.setInt(3, pageSize);
            } else {
                stmt.setInt(1, start);
                stmt.setInt(2, pageSize);
            }

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                scoreList.add(convertToStudentScore(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return scoreList;
    }

    /**
     * 查询总记录数
     */
    public int findTotalCount(Integer subjectId) {
        String sql = "SELECT COUNT(*) FROM student_score";
        if (subjectId != null) {
            sql += " WHERE subject_id = ?";
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            if (subjectId != null) {
                stmt.setInt(1, subjectId);
            }

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据学生ID分页查询成绩
     */
    public List<StudentScore> findByStudentIdAndPage(Integer studentId, Integer subjectId, int start, int pageSize) {
        StringBuilder sql = new StringBuilder(
                "SELECT ss.*, si.subject_name, " +
                        "t.real_name as teacher_name, " +
                        "s.real_name as student_name " +
                        "FROM student_score ss " +
                        "LEFT JOIN subject_info si ON ss.subject_id = si.subject_id " +
                        "LEFT JOIN user s ON ss.student_id = s.user_id " +
                        "LEFT JOIN user t ON si.teacher_id = t.user_id " +
                        "WHERE ss.student_id = ? "
        );

        if (subjectId != null) {
            sql.append("AND ss.subject_id = ? ");
        }

        sql.append("ORDER BY ss.score_id DESC LIMIT ?, ?");

        List<StudentScore> scoreList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            int paramIndex = 1;
            stmt.setInt(paramIndex++, studentId);

            if (subjectId != null) {
                stmt.setInt(paramIndex++, subjectId);
            }

            stmt.setInt(paramIndex++, start);
            stmt.setInt(paramIndex, pageSize);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                StudentScore score = convertToStudentScore(rs);
                score.setTeacherName(rs.getString("teacher_name"));
                score.setStudentName(rs.getString("student_name"));
                scoreList.add(score);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return scoreList;
    }

    /**
     * 查询学生成绩总数
     */
    public int findTotalCountByStudentId(Integer studentId, Integer subjectId) {
        StringBuilder sql = new StringBuilder(
                "SELECT COUNT(*) FROM student_score WHERE student_id = ? "
        );

        if (subjectId != null) {
            sql.append("AND subject_id = ? ");
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            int paramIndex = 1;
            stmt.setInt(paramIndex++, studentId);

            if (subjectId != null) {
                stmt.setInt(paramIndex, subjectId);
            }

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}